package cc.linker.steplink.crm.merchant.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.awt.Color;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 导出  单表头 多表头  多sheet页导出
 *
 * @author qhp
 */
public class ExcelUtil {


    /**
     * 用于正则表达式判断private static修饰可以更快匹配
     */
    private static Pattern pattern = Pattern.compile("-?[0-9]*\\.?[0-9]+");

    /**
     * 外网166 提供给上报数据模块使用
     *
     * @param imgUrl
     * @return
     */
    public static String replaceImgStr(String imgUrl) {
        //线上是D盘
        //imgUrl = imgUrl.replace("D:\\apache-tomcat-9.0.21\\webapps", "http:\\117.148.148.166:8081");
        //线下是D盘
        imgUrl = imgUrl.replace("D:\\apache-tomcat-9.0.21\\webapps", "http:\\192.168.1.40:6359");
        return imgUrl;
    }

    /**
     * 内网66 提供给问题反馈模块使用
     *
     * @param imgUrl
     * @return
     */
    public static String replaceImgStrForSuggestion(String imgUrl) {
        //线上是E盘
        //imgUrl = imgUrl.replace("E:\\apache-tomcat-9.0.21\\webapps", "http:\\10.79.72.66:8080");
        //线下是D盘
        imgUrl = imgUrl.replace("D:\\apache-tomcat-9.0.21\\webapps", "http:\\192.168.1.40:6359");
        return imgUrl;
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        for (int i = 0; i < 32; i++) {
            sheet.setColumnWidth(i, 256 * 20 + 184);
        }
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        //设置字体大小
        font.setFontHeightInPoints((short) 14);
        style.setFont(font);
        //声明列对象
        HSSFCell cell = null;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                cell = row.createCell(j);
                if (values[i][j] == null || "".equals(values[i][j])) {
                    continue;
                } else {
                    cell.setCellValue(values[i][j]);
                }
            }
        }
        return wb;
    }

    /**
     * 导出Excel
     *
     * @param sheetName sheet名称
     * @param title     标题1
     * @param values    内容
     * @param wb        HSSFWorkbook对象
     * @param title1    标题2
     * @param headnum0  标题1位置
     * @param headnum1  标题2位置
     * @return 两行列名
     */
    public static HSSFWorkbook getHSSFWorkbookTwo(String sheetName, String[] title, String[] title1, String[] headnum0, String[] headnum1, String[][] values, HSSFWorkbook wb) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
            HSSFCellStyle cellStyle = wb.createCellStyle();
            // 设置背景色
            cellStyle.setFillForegroundColor((short) 13);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //下边框
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //左边框
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            //上边框
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //右边框
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        for (int i = 0; i < 47; i++) {
            sheet.setColumnWidth(i, 256 * 20 + 184);
        }
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        //设置字体大小
        font.setFontHeightInPoints((short) 14);
        style.setFont(font);
        //声明列对象
        HSSFCell cell = null;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        for (int i = 0; i < headnum0.length; i++) {
            String[] temp = headnum0[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }
        row = sheet.createRow(1);
        for (int i = 0; i < title1.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title1[i]);
            cell.setCellStyle(style);
        }
        //动态合并单元格
        for (int i = 0; i < headnum1.length; i++) {
            String[] temp = headnum1[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 2);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                cell = row.createCell(j);
                if (values[i][j] == null || "".equals(values[i][j])) {
                    continue;
                } else {
                    cell.setCellValue(values[i][j]);
                }
            }
        }
        return wb;
    }

    public static void setResponseHeader(HttpServletRequest request, HttpServletResponse response, String fileName) {
        try {
            final String userAgent = request.getHeader("USER-AGENT").toLowerCase();
            response.reset();
            response.setCharacterEncoding("UTF-8");
            if (userAgent.contains("MSIE")) {
                //IE浏览器
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else if (userAgent.contains("MOZILLA") || userAgent.contains("FIREFOX")) {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } else if (userAgent.contains("EDGE")) {
                fileName = URLEncoder.encode(fileName, "UTF-8").replace("+", " ");
            } else {
                //其他浏览器
                fileName = URLEncoder.encode(fileName, "UTF-8");
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static boolean isNumeric(String str) {
        Matcher isNum = pattern.matcher(str);
        if (!isNum.matches()) {
            return false;
        }
        return true;
    }

    /**
     * @param list
     * @param sheet
     */
    public static void multipleExcel(@NotNull List<Map<String, Object>> list, XSSFSheet sheet) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;
                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());

                }
            }
        }
    }
    /**
     * @param list
     * @param sheet
     */
    public static void multipleExcel1(@NotNull List<Map<String, Object>> list, XSSFSheet sheet,XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);
        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));

            XSSFCell cell = null;
            cell = rowRowName.createCell(n, HSSFCell.CELL_TYPE_STRING);
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            switch (n) {
                case 0:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 1:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 2:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 3:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 4:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 5:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 6:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.GREEN.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 7:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.CORAL.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 8:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.RED.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                case 9:
                    //设置前景颜色
                    cellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.index);
                    //设置颜色填充规则
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    break;
                default:
            }
            cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
            cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
            cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
            cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
            cell.setCellStyle(cellStyle);
            cellRowName.setCellValue(text);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;
                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    /**
     * 将临时文件下载到本地并删除临时文件
     *
     * @param tempFile 文件路径
     * @param workbook 工作表
     * @param fileName 文件名
     * @param response
     */
    public static void fileExcel(File tempFile, XSSFWorkbook workbook, String fileName, HttpServletResponse response) {

        FileOutputStream out = null;
        FileInputStream in = null;
        OutputStream out1 = null;
        try {
            out = new FileOutputStream(tempFile);
            workbook.write(out);
            out1 = response.getOutputStream();
            byte[] b = new byte[1024];
            response.setCharacterEncoding("utf-8");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("content-disposition", "attachment; filename=" + fileName);
            in = new FileInputStream(tempFile);
            int n;
            while ((n = in.read(b)) != -1) {
                out1.write(b, 0, n);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {

                    e.printStackTrace();
                }
            }
            if (out1 != null) {
                try {
                    out1.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (tempFile.exists()) {
                tempFile.delete();
            }
        }

    }

    /**
     * 将临时文件下载到服务器
     *
     * @param tempFile 文件路径
     * @param workbook 工作表
     * @param fileName 文件名
     */
    public static void fileExcel2(File tempFile, XSSFWorkbook workbook, String fileName) {

        if (tempFile.exists()) {
            tempFile.delete();
        }

        FileOutputStream out = null;
        FileInputStream in = null;
        try {
            out = new FileOutputStream(tempFile);
            workbook.write(out);
            byte[] b = new byte[1024];
            fileName = URLEncoder.encode(fileName, "UTF-8");

            in = new FileInputStream(tempFile);


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {

                    e.printStackTrace();
                }
            }
        }

    }

    public static List<List<String>> readXls(String path) throws Exception {
        InputStream is = new FileInputStream(path);

        // HSSFWorkbook 标识整个excel
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
        List<List<String>> result = new ArrayList<List<String>>();
        int size = hssfWorkbook.getNumberOfSheets();
        // 循环每一页，并处理当前循环页
        for (int numSheet = 0; numSheet < size; numSheet++) {
            // HSSFSheet 标识某一页
            XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 处理当前页，循环读取每一行
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                // HSSFRow表示行
               XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                int minColIx = hssfRow.getFirstCellNum();
                int maxColIx = hssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                // 遍历改行，获取处理每个cell元素
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    // HSSFCell 表示单元格
                   XSSFCell cell = hssfRow.getCell(colIx);
                    if (cell == null) {
                        continue;
                    }
                    rowList.add(getStringVal(cell));
                }
                result.add(rowList);
            }
        }
        return result;
    }


    public static String getStringVal(XSSFCell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                cell.setCellType(Cell.CELL_TYPE_STRING);
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }

    public static void multipleExcelPoleList(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs5 = new String[]{"七米,八米,九米,十米,十一米,十二米,十二米以上"};
                String[] strs8 = new String[] { "自建,共建,租用,购买,置换" };
                String[] strs9 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力,中国铁塔,国动" };
                String[] strs10 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力" };
                String[] strs11 = new String[] { "自用,出租,共享" };
                String[] strs13 = new String[] { "普通杆,H型杆,高桩杆,A型杆,单接杆,L型杆,双接杆" };
                String[] strs14 = new String[] { "角拉,人字拉,三方拉,四方拉,终端拉,高桩拉,其它,无" };
                String[] strs15 = new String[] { "规划,设计,工程,在网,维护,退网,空载" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint5 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs5);
                XSSFDataValidationConstraint dvConstraint8 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs8);
                XSSFDataValidationConstraint dvConstraint9 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs9);
                XSSFDataValidationConstraint dvConstraint10 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs10);
                XSSFDataValidationConstraint dvConstraint11 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs11);
                XSSFDataValidationConstraint dvConstraint13 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs13);
                XSSFDataValidationConstraint dvConstraint14 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs14);
                XSSFDataValidationConstraint dvConstraint15 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs15);

                CellRangeAddressList addressList5 = new CellRangeAddressList(0, 100, 5, 5);
                CellRangeAddressList addressList8 = new CellRangeAddressList(0, 100, 8, 8);
                CellRangeAddressList addressList9 = new CellRangeAddressList(0, 100, 9, 9);
                CellRangeAddressList addressList10 = new CellRangeAddressList(0, 100, 10, 10);
                CellRangeAddressList addressList11 = new CellRangeAddressList(0, 100, 11, 11);
                CellRangeAddressList addressList13 = new CellRangeAddressList(0, 100, 13, 13);
                CellRangeAddressList addressList14 = new CellRangeAddressList(0, 100, 14, 14);
                CellRangeAddressList addressList15 = new CellRangeAddressList(0, 100, 15, 15);

                XSSFDataValidation validation5 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint5, addressList5);
                XSSFDataValidation validation8 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint8, addressList8);
                XSSFDataValidation validation9 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint9, addressList9);
                XSSFDataValidation validation10 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint10, addressList10);
                XSSFDataValidation validation11 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint11, addressList11);
                XSSFDataValidation validation13 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint13, addressList13);
                XSSFDataValidation validation14 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint14, addressList14);
                XSSFDataValidation validation15 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint15, addressList15);

                sheet.addValidationData(validation5);
                sheet.addValidationData(validation8);
                sheet.addValidationData(validation9);
                sheet.addValidationData(validation10);
                sheet.addValidationData(validation11);
                sheet.addValidationData(validation13);
                sheet.addValidationData(validation14);
                sheet.addValidationData(validation15);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());

                }
            }
        }
    }


    public static void multipleBarSection(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs11 = new String[] { "无,电信,联通,电信+联通" };
                String[] strs12 = new String[] { "无,电信,联通,电信+联通" };
                String[] strs14 = new String[] { "自建,共建,租用,购买,置换" };
                String[] strs15 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力,中国铁塔,国动" };
                String[] strs16 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力" };
                String[] strs17 = new String[] { "C1,C2,C3,C4,驻地网" };
                String[] strs18 = new String[] { "钢绞线,钢心铝绞线,铁线,其它" };
                String[] strs24 = new String[] { "跨河,跨村路,跨高速公路,跨铁路,交越,无" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint11 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs11);
                XSSFDataValidationConstraint dvConstraint12 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs12);
                XSSFDataValidationConstraint dvConstraint14 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs14);
                XSSFDataValidationConstraint dvConstraint15 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs15);
                XSSFDataValidationConstraint dvConstraint16 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs16);
                XSSFDataValidationConstraint dvConstraint17 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs17);
                XSSFDataValidationConstraint dvConstraint18 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs18);
                XSSFDataValidationConstraint dvConstraint24 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs24);

                CellRangeAddressList addressList11 = new CellRangeAddressList(0, 100, 11, 11);
                CellRangeAddressList addressList12 = new CellRangeAddressList(0, 100, 12, 12);
                CellRangeAddressList addressList14 = new CellRangeAddressList(0, 100, 14, 14);
                CellRangeAddressList addressList15 = new CellRangeAddressList(0, 100, 15, 15);
                CellRangeAddressList addressList16 = new CellRangeAddressList(0, 100, 16, 16);
                CellRangeAddressList addressList17 = new CellRangeAddressList(0, 100, 17, 17);
                CellRangeAddressList addressList18 = new CellRangeAddressList(0, 100, 18, 18);
                CellRangeAddressList addressList24 = new CellRangeAddressList(0, 100, 24, 24);

                XSSFDataValidation validation11 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint11, addressList11);
                XSSFDataValidation validation12 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint12, addressList12);
                XSSFDataValidation validation14 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint14, addressList14);
                XSSFDataValidation validation15 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint15, addressList15);
                XSSFDataValidation validation16 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint16, addressList16);
                XSSFDataValidation validation17 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint17, addressList17);
                XSSFDataValidation validation18 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint18, addressList18);
                XSSFDataValidation validation24 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint24, addressList24);

                sheet.addValidationData(validation11);
                sheet.addValidationData(validation12);
                sheet.addValidationData(validation14);
                sheet.addValidationData(validation15);
                sheet.addValidationData(validation16);
                sheet.addValidationData(validation17);
                sheet.addValidationData(validation18);
                sheet.addValidationData(validation24);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleManpowerWell(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs11 = new String[] { "无,电信,联通,电信+联通" };
                String[] strs12 = new String[] { "无,电信,联通,电信+联通" };
                String[] strs14 = new String[] { "自建,共建,租用,购买,置换" };
                String[] strs15 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力,中国铁塔,国动" };
                String[] strs16 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力" };
                String[] strs17 = new String[] { "C1,C2,C3,C4,驻地网" };
                String[] strs18 = new String[] { "管道段,引上管" };
                String[] strs19 = new String[] { "水泥,钢,PVC,硅,陶瓷,其他" };
                String[] strs21 = new String[] { "规划,设计,工程,在网,维护,退网,空载" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint11 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs11);
                XSSFDataValidationConstraint dvConstraint12 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs12);
                XSSFDataValidationConstraint dvConstraint14 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs14);
                XSSFDataValidationConstraint dvConstraint15 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs15);
                XSSFDataValidationConstraint dvConstraint16 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs16);
                XSSFDataValidationConstraint dvConstraint17 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs17);
                XSSFDataValidationConstraint dvConstraint18 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs18);
                XSSFDataValidationConstraint dvConstraint19 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs19);
                XSSFDataValidationConstraint dvConstraint21 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs21);

                CellRangeAddressList addressList11 = new CellRangeAddressList(0, 100, 11, 11);
                CellRangeAddressList addressList12 = new CellRangeAddressList(0, 100, 12, 12);
                CellRangeAddressList addressList14 = new CellRangeAddressList(0, 100, 14, 14);
                CellRangeAddressList addressList15 = new CellRangeAddressList(0, 100, 15, 15);
                CellRangeAddressList addressList16 = new CellRangeAddressList(0, 100, 16, 16);
                CellRangeAddressList addressList17 = new CellRangeAddressList(0, 100, 17, 17);
                CellRangeAddressList addressList18 = new CellRangeAddressList(0, 100, 18, 18);
                CellRangeAddressList addressList19 = new CellRangeAddressList(0, 100, 19, 19);
                CellRangeAddressList addressList21 = new CellRangeAddressList(0, 100, 21, 21);

                XSSFDataValidation validation11 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint11, addressList11);
                XSSFDataValidation validation12 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint12, addressList12);
                XSSFDataValidation validation14 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint14, addressList14);
                XSSFDataValidation validation15 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint15, addressList15);
                XSSFDataValidation validation16 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint16, addressList16);
                XSSFDataValidation validation17 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint17, addressList17);
                XSSFDataValidation validation18 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint18, addressList18);
                XSSFDataValidation validation19 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint19, addressList19);
                XSSFDataValidation validation21 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint21, addressList21);

                sheet.addValidationData(validation11);
                sheet.addValidationData(validation12);
                sheet.addValidationData(validation14);
                sheet.addValidationData(validation15);
                sheet.addValidationData(validation16);
                sheet.addValidationData(validation17);
                sheet.addValidationData(validation18);
                sheet.addValidationData(validation19);
                sheet.addValidationData(validation21);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    /**
     * @param list
     * @param sheet
     * @Time 2018-11-13
     */
    public static void multipleExcel2(@NotNull List<Map<String, Object>> list, XSSFSheet sheet,XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleExcel3(@NotNull List<Map<String, Object>> list, XSSFSheet sheet,XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multiplePipeSegment(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs11 = new String[] { "无,电信,联通,电信+联通" };
                String[] strs12 = new String[] { "无,电信,联通,电信+联通" };
                String[] strs14 = new String[] { "自建,共建,租用,购买,置换" };
                String[] strs15 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力,中国铁塔,国动" };
                String[] strs16 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力" };
                String[] strs17 = new String[] { "C1,C2,C3,C4,驻地网" };
                String[] strs18 = new String[] { "管道段,引上管" };
                String[] strs19 = new String[] { "水泥,钢,PVC,硅,陶瓷,其他" };
                String[] strs21 = new String[] { "规划,设计,工程,在网,维护,退网,空载" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint11 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs11);
                XSSFDataValidationConstraint dvConstraint12 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs12);
                XSSFDataValidationConstraint dvConstraint14 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs14);
                XSSFDataValidationConstraint dvConstraint15 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs15);
                XSSFDataValidationConstraint dvConstraint16 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs16);
                XSSFDataValidationConstraint dvConstraint17 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs17);
                XSSFDataValidationConstraint dvConstraint18 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs18);
                XSSFDataValidationConstraint dvConstraint19 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs19);
                XSSFDataValidationConstraint dvConstraint21 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs21);

                CellRangeAddressList addressList11 = new CellRangeAddressList(0, 100, 11, 11);
                CellRangeAddressList addressList12 = new CellRangeAddressList(0, 100, 12, 12);
                CellRangeAddressList addressList14 = new CellRangeAddressList(0, 100, 14, 14);
                CellRangeAddressList addressList15 = new CellRangeAddressList(0, 100, 15, 15);
                CellRangeAddressList addressList16 = new CellRangeAddressList(0, 100, 16, 16);
                CellRangeAddressList addressList17 = new CellRangeAddressList(0, 100, 17, 17);
                CellRangeAddressList addressList18 = new CellRangeAddressList(0, 100, 18, 18);
                CellRangeAddressList addressList19 = new CellRangeAddressList(0, 100, 19, 19);
                CellRangeAddressList addressList21 = new CellRangeAddressList(0, 100, 21, 21);


                XSSFDataValidation validation11 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint11, addressList11);
                XSSFDataValidation validation12 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint12, addressList12);
                XSSFDataValidation validation14 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint14, addressList14);
                XSSFDataValidation validation15 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint15, addressList15);
                XSSFDataValidation validation16 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint16, addressList16);
                XSSFDataValidation validation17 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint17, addressList17);
                XSSFDataValidation validation18 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint18, addressList18);
                XSSFDataValidation validation19 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint19, addressList19);
                XSSFDataValidation validation21 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint21, addressList21);

                sheet.addValidationData(validation11);
                sheet.addValidationData(validation12);
                sheet.addValidationData(validation14);
                sheet.addValidationData(validation15);
                sheet.addValidationData(validation16);
                sheet.addValidationData(validation17);
                sheet.addValidationData(validation18);
                sheet.addValidationData(validation19);
                sheet.addValidationData(validation21);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleOpticalcable(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs4 = new String[] { "C1,C2,骨干层,汇聚层,接入主配线层,接入辅配线层,接入引入层" };
                String[] strs5 = new String[] { "路缆,海缆" };
                String[] strs8 = new String[] { "自建,共建,租用,购买,置换" };
                String[] strs9 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力,中国铁塔,国动" };
                String[] strs10 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力" };
                String[] strs11 = new String[] { "规划,设计,工程,在网,维护,退网,空载" };
                String[] strs12 = new String[] { "主干光缆,配线光缆,中继光缆,联络光缆,局内光缆,接入光缆" };
                String[] strs15 = new String[] { "驻地网光缆,无线基站接入,无线延伸系统,政企客户接入,WLAN,其他" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint4 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs4);
                XSSFDataValidationConstraint dvConstraint5 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs5);
                XSSFDataValidationConstraint dvConstraint8 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs8);
                XSSFDataValidationConstraint dvConstraint9 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs9);
                XSSFDataValidationConstraint dvConstraint10 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs10);
                XSSFDataValidationConstraint dvConstraint11 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs11);
                XSSFDataValidationConstraint dvConstraint12 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs12);
                XSSFDataValidationConstraint dvConstraint15 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs15);

                CellRangeAddressList addressList4 = new CellRangeAddressList(0, 100, 4, 4);
                CellRangeAddressList addressList5 = new CellRangeAddressList(0, 100, 5, 5);
                CellRangeAddressList addressList8 = new CellRangeAddressList(0, 100, 8, 8);
                CellRangeAddressList addressList9 = new CellRangeAddressList(0, 100, 9, 9);
                CellRangeAddressList addressList10 = new CellRangeAddressList(0, 100, 10, 10);
                CellRangeAddressList addressList11 = new CellRangeAddressList(0, 100, 11, 11);
                CellRangeAddressList addressList12 = new CellRangeAddressList(0, 100, 12, 12);
                CellRangeAddressList addressList15 = new CellRangeAddressList(0, 100, 15, 15);


                XSSFDataValidation validation4 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint4, addressList4);
                XSSFDataValidation validation5 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint5, addressList5);
                XSSFDataValidation validation8 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint8, addressList8);
                XSSFDataValidation validation9 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint9, addressList9);
                XSSFDataValidation validation10 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint10, addressList10);
                XSSFDataValidation validation11 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint11, addressList11);
                XSSFDataValidation validation12 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint12, addressList12);
                XSSFDataValidation validation15 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint15, addressList15);

                sheet.addValidationData(validation4);
                sheet.addValidationData(validation5);
                sheet.addValidationData(validation8);
                sheet.addValidationData(validation9);
                sheet.addValidationData(validation10);
                sheet.addValidationData(validation11);
                sheet.addValidationData(validation12);
                sheet.addValidationData(validation15);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleOpticalcableLocation(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs6 = new String[] { "GYTS,GYTA,GYTY,GYFTY,GYXTW,GYSTA,GYTY53,GJYPFH-2B,GJYXDCH-2D,GJYXFHA" };
                String[] strs8 = new String[] { "C1,C2,骨干层,汇聚层,接入主配线层,接入辅配线层,接入引入层" };
                String[] strs9 = new String[] { "G.652性能最佳光纤,G.653色散最佳光纤,G.654损耗最佳光纤,G.655损耗最佳光纤,色散平坦光纤,未知" };
                String[] strs10 = new String[] { "管道,直埋,混合,架空,墙挂" };
                String[] strs12 = new String[] { "自建,共建,租用,购买,置换" };
                String[] strs13 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力,中国铁塔,国动" };
                String[] strs14 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力" };
                String[] strs15 = new String[] { "自用,出租,共享" };
                String[] strs23 = new String[] { "规划,设计,工程,在网,维护,退网,空载" };
                String[] strs24 = new String[] { "驻地网光缆,无线基站接入,无线延伸系统,政企客户接入,WLAN,其他" };


                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint6 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs6);
                XSSFDataValidationConstraint dvConstraint8 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs8);
                XSSFDataValidationConstraint dvConstraint9 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs9);
                XSSFDataValidationConstraint dvConstraint10 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs10);
                XSSFDataValidationConstraint dvConstraint12 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs12);
                XSSFDataValidationConstraint dvConstraint13 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs13);
                XSSFDataValidationConstraint dvConstraint14 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs14);
                XSSFDataValidationConstraint dvConstraint15 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs15);
                XSSFDataValidationConstraint dvConstraint23 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs23);
                XSSFDataValidationConstraint dvConstraint24 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs24);

                CellRangeAddressList addressList6 = new CellRangeAddressList(0, 100, 6, 6);
                CellRangeAddressList addressList8 = new CellRangeAddressList(0, 100, 8, 8);
                CellRangeAddressList addressList9 = new CellRangeAddressList(0, 100, 9, 9);
                CellRangeAddressList addressList10 = new CellRangeAddressList(0, 100, 10, 10);
                CellRangeAddressList addressList12 = new CellRangeAddressList(0, 100, 12, 12);
                CellRangeAddressList addressList13 = new CellRangeAddressList(0, 100, 13, 13);
                CellRangeAddressList addressList14 = new CellRangeAddressList(0, 100, 14, 14);
                CellRangeAddressList addressList15 = new CellRangeAddressList(0, 100, 15, 15);
                CellRangeAddressList addressList23 = new CellRangeAddressList(0, 100, 23, 23);
                CellRangeAddressList addressList24 = new CellRangeAddressList(0, 100, 24, 24);

                XSSFDataValidation validation6 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint6, addressList6);
                XSSFDataValidation validation8 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint8, addressList8);
                XSSFDataValidation validation9 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint9, addressList9);
                XSSFDataValidation validation10 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint10, addressList10);
                XSSFDataValidation validation12 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint12, addressList12);
                XSSFDataValidation validation13 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint13, addressList13);
                XSSFDataValidation validation14 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint14, addressList14);
                XSSFDataValidation validation15 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint15, addressList15);
                XSSFDataValidation validation23 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint23, addressList23);
                XSSFDataValidation validation24 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint24, addressList24);

                sheet.addValidationData(validation6);
                sheet.addValidationData(validation8);
                sheet.addValidationData(validation9);
                sheet.addValidationData(validation10);
                sheet.addValidationData(validation12);
                sheet.addValidationData(validation13);
                sheet.addValidationData(validation14);
                sheet.addValidationData(validation15);
                sheet.addValidationData(validation23);
                sheet.addValidationData(validation24);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleWellSite(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs5 = new String[] { "规划,设计,工程,在网,维护,退网,空载" };
                String[] strs8 = new String[] { "C1,C2,C3,C4,驻地网" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint5 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs5);
                XSSFDataValidationConstraint dvConstraint8 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs8);

                CellRangeAddressList addressList5 = new CellRangeAddressList(0, 100, 5, 5);
                CellRangeAddressList addressList8 = new CellRangeAddressList(0, 100, 8, 8);


                XSSFDataValidation validation5 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint5, addressList5);
                XSSFDataValidation validation8 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint8, addressList8);

                sheet.addValidationData(validation5);
                sheet.addValidationData(validation8);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleWallHanging(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs4 = new String[] { "规划,设计,工程,在网,维护,退网,空载" };
                String[] strs7 = new String[] { "C1,C2,C3,C4,驻地网" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint4 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs4);
                XSSFDataValidationConstraint dvConstraint7 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs7);

                CellRangeAddressList addressList4 = new CellRangeAddressList(0, 100, 4, 4);
                CellRangeAddressList addressList7 = new CellRangeAddressList(0, 100, 7, 7);


                XSSFDataValidation validation4 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint4, addressList4);
                XSSFDataValidation validation7 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint7, addressList7);

                sheet.addValidationData(validation4);
                sheet.addValidationData(validation7);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleWallSection(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs10 = new String[] { "自建,共建,租用,购买,置换,免费使用" };
                String[] strs11 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力,中国铁塔,国动" };
                String[] strs12 = new String[] { "中国移动,中国联通,中国电信,中国铁通,广电,业主,电力" };
                String[] strs15 = new String[] { "自用,出租,共享" };
                String[] strs17 = new String[] { "规划,设计,工程,在网,维护,退网,空载" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint10 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs10);
                XSSFDataValidationConstraint dvConstraint11 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs11);
                XSSFDataValidationConstraint dvConstraint12 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs12);
                XSSFDataValidationConstraint dvConstraint15 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs15);
                XSSFDataValidationConstraint dvConstraint17 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs17);

                CellRangeAddressList addressList10 = new CellRangeAddressList(0, 100, 10, 10);
                CellRangeAddressList addressList11 = new CellRangeAddressList(0, 100, 11, 11);
                CellRangeAddressList addressList12 = new CellRangeAddressList(0, 100, 12, 12);
                CellRangeAddressList addressList15 = new CellRangeAddressList(0, 100, 15, 15);
                CellRangeAddressList addressList17 = new CellRangeAddressList(0, 100, 17, 17);


                XSSFDataValidation validation10 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint10, addressList10);
                XSSFDataValidation validation11 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint11, addressList11);
                XSSFDataValidation validation12 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint12, addressList12);
                XSSFDataValidation validation15 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint15, addressList15);
                XSSFDataValidation validation17 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint17, addressList17);

                sheet.addValidationData(validation10);
                sheet.addValidationData(validation11);
                sheet.addValidationData(validation12);
                sheet.addValidationData(validation15);
                sheet.addValidationData(validation17);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleWallPoint(@NotNull List<Map<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs1 = new String[] { "人工定位,GPS" };
                String[] strs12 = new String[] { "是,否" };
                String[] strs13 = new String[] { "是,否" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint1 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs1);
                XSSFDataValidationConstraint dvConstraint12 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs12);
                XSSFDataValidationConstraint dvConstraint13 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs13);

                CellRangeAddressList addressList1 = new CellRangeAddressList(0, 100, 1, 1);
                CellRangeAddressList addressList12 = new CellRangeAddressList(0, 100, 12, 12);
                CellRangeAddressList addressList13 = new CellRangeAddressList(0, 100, 13, 13);


                XSSFDataValidation validation1 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint1, addressList1);
                XSSFDataValidation validation12 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint12, addressList12);
                XSSFDataValidation validation13 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint13, addressList13);

                sheet.addValidationData(validation1);
                sheet.addValidationData(validation12);
                sheet.addValidationData(validation13);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

    public static void multipleExceltxListMap(@NotNull List<LinkedHashMap<String, Object>> list, XSSFSheet sheet, XSSFWorkbook workbook) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        // 取下标为0得元素
        if (list == null || list.size() <= 0) {
            return;
        }
        Map s = list.get(0);
        // 动态获取表头
        Set<String> set = s.keySet();
        List<String> rowName = new ArrayList<>(set);
        // 循环所有数据
        for (int i = 0; i < list.size(); i++) {
            Map h = list.get(i);
            // 创建一个数组
            Object[] a = new Object[rowName.size()];
            // 根据表头获取值
            for (int j = 0; j < set.size(); j++) {
                a[j] = h.get(rowName.get(j));
            }
            // 存入集合中
            dataList.add(a);
        }

        // 定义所需列数
        int columnNum = rowName.size();
        XSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            XSSFCell cellRowName = rowRowName.createCell(n);
            //设置表头颜色
            XSSFCellStyle header = workbook.createCellStyle();
            header.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
            header.setFillForegroundColor(new XSSFColor(new Color(247, 255, 67)));

            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 设置列头单元格的值
            XSSFRichTextString text = new XSSFRichTextString(rowName.get(n));
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(header);
        }
        // 将查询出的数据设置到sheet对应的单元格中
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < obj.length; j++) {
                // 设置单元格的数据类型
                XSSFCell cell = null;

                //设置下拉框样式
                String[] strs4 = new String[] { "第一平台,第二平台,第三平台,其他" };
                String[] strs16 = new String[] { "单频单极化,单频双极化,单频双极化电调,双频双极化,双频双极化电调,三频双极化,三频双极化电调" };
                String[] strs17 = new String[] { "板状天线,八木天线,鞭状天线,抛物面天线,其它" };
                String[] strs18 = new String[] { "美化树,水箱,空调箱,外墙,无,箱体,美化外罩,水桶,广告牌,方柱,排气管,集束天线,广告灯箱,射灯,圆柱,路灯,假石,太阳能,监控摄像头,落地景观塔,监控杆" };
                String[] strs25 = new String[] { "收,发,收发共用" };
                String[] strs26 = new String[] { "单TD,TD+GSM,TD+GSM+DCS,TD+DCS,TD+TDL,单GSM,单DCS,单TDL,GSM+DCS,GSM+DCS+TDS+TD,单FA,单D,FAE,FADGSM+DCS+FA,GSM+FA,GSM+FAD,DCS+D,GSM+DCS+FAD,GSM+DCS+TDS+TDL,TD+GSM+TDL,TD+DCS+TDL" };
                String[] strs27 = new String[] { "是,否" };

                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);

                XSSFDataValidationConstraint dvConstraint4 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs4);
                XSSFDataValidationConstraint dvConstraint16 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs16);
                XSSFDataValidationConstraint dvConstraint17 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs17);
                XSSFDataValidationConstraint dvConstraint18 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs18);
                XSSFDataValidationConstraint dvConstraint25 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs25);
                XSSFDataValidationConstraint dvConstraint26 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs26);
                XSSFDataValidationConstraint dvConstraint27 = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(strs27);

                CellRangeAddressList addressList4 = new CellRangeAddressList(0, 100, 4, 4);
                CellRangeAddressList addressList16 = new CellRangeAddressList(0, 100, 16, 16);
                CellRangeAddressList addressList17 = new CellRangeAddressList(0, 100, 17, 17);
                CellRangeAddressList addressList18 = new CellRangeAddressList(0, 100, 18, 18);
                CellRangeAddressList addressList25 = new CellRangeAddressList(0, 100, 25, 25);
                CellRangeAddressList addressList26 = new CellRangeAddressList(0, 100, 26, 26);
                CellRangeAddressList addressList27 = new CellRangeAddressList(0, 100, 27, 27);

                XSSFDataValidation validation10 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint4, addressList4);
                XSSFDataValidation validation14 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint16, addressList16);
                XSSFDataValidation validation17 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint17, addressList17);
                XSSFDataValidation validation18 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint18, addressList18);
                XSSFDataValidation validation25 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint25, addressList25);
                XSSFDataValidation validation26 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint26, addressList26);
                XSSFDataValidation validation27 = (XSSFDataValidation) dvHelper.createValidation(dvConstraint27, addressList27);

                sheet.addValidationData(validation10);
                sheet.addValidationData(validation14);
                sheet.addValidationData(validation17);
                sheet.addValidationData(validation18);
                sheet.addValidationData(validation25);
                sheet.addValidationData(validation26);
                sheet.addValidationData(validation27);

                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);

                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
            }
        }
    }

}

